alter proc rpt_AI_AccountBalance
@date datetime,
@level int
as
begin
	
	declare @firstDate as datetime
	set @firstDate = DATEADD(mm, DATEDIFF(mm, 0, @date), 0)
	declare @lastDate as datetime
	set @lastDate = dateadd(dd,-1,dateadd(mm,datediff(mm,0,@date)+1,0))
	
	declare @firstLM as datetime
	set @firstLM = DATEADD(MM, -1, @firstDate)
	declare @lastLM as datetime
	set @lastLM = DATEADD(MM, -1, @lastDate)

	declare @dateLM as datetime
	set @dateLM = DATEADD(MM, -1, @date)
	
	declare @result table
	(		
		LocalAcctCode nvarchar(100),
		AcctName nvarchar(100),
		AcctFullName nvarchar(100), 
		IsFather int,
		Levels int,
		GroupMask int, 
		GrpLine int,
		FatherNum nvarchar(100)
	)
	insert into @result select ac.AcctCode, ac.AcctName, 
	case ac.Levels when 2 then '     ' when 3 then '          ' when 4 then '                 ' when 5 then '                            ' else '' end + ac.AcctCode + ' - ' + ac.AcctName, 0, ac.Levels, ac.GroupMask, ac.GrpLine, ac.FatherNum from OACT ac 
	where ac.GroupMask in (1)

		-- update is father
	--have child or not
	update @result set
	IsFather = case when (select COUNT(*) from OACT ct where FatherNum = LocalAcctCode  collate SQL_Latin1_General_CP850_CI_AS)>0 then 1 else 0 end

	declare @las table
	(		
		LocalAcctCode nvarchar(100),
		AcctName nvarchar(100),
		AcctFullName nvarchar(100), 
		IsFather int,
		curAmt numeric(19, 2),
		prevAmt numeric(19, 2),
		FatherNum nvarchar(100),
		Levels int
	)
	--insert into @las 
	--select re.LocalAcctCode, re.AcctName, re.AcctFullName, re.IsFather, SUM(isnull(T.curAmt, 0)) curAmt, Sum(isnull(T.prevAmt, 0))prevAmt, re.FatherNum, re.Levels 
	--from @result re
	-- join
	--(
	--	select jd.Account, isnull(jd.Debit, 0) - isnull(jd.Credit, 0) curAmt, 0 prevAmt from JDT1 jd where jd.RefDate <= @date
		
	--	union 
	--	select jd.Account, 0 curAmt, isnull(jd.Debit, 0) - isnull(jd.Credit, 0) prevAmt from JDT1 jd where jd.RefDate <= @dateLM
	--)T on re.LocalAcctCode = T.Account collate SQL_Latin1_General_CP850_CI_AS
	--group by re.LOcalAcctCode, re.AcctName, re.AcctFullName, re.IsFather, re.GroupMask, re.GrpLine, re.FatherNum, re.Levels
	-- order by re.GroupMask, re.GrpLine
	 
	 --declare @noOfLevel as int
	 --set @noOfLevel =  @level
	 --while @noOfLevel < 5
	 --begin
		-- update @las set curAmt = isnull(T.curAmt, 0)
		--from @las lb join 
		--(select lc.FatherNum, Sum(isnull(lc.curAmt, 0)) curAmt from @las lc group by lc.FatherNum) T on T.FatherNum = lb.LocalAcctCode
 
		--update @las set prevAmt = isnull(T.prevAmt, 0)
		--from @las lb join 
		--(select lc.FatherNum, Sum(isnull(lc.prevAmt, 0)) prevAmt from @las lc group by lc.FatherNum) T on T.FatherNum = lb.LocalAcctCode
		
		--set @noOfLevel = @noOfLevel + 1 
	 --end
	 
	 --print @lastDate
	 
	 
	 insert into @las 
	select '', '', '', 0, SUM(isnull(T.curAmt, 0)) curAmt, Sum(isnull(T.prevAmt, 0))prevAmt, '', 0 
	from 
	
	(
		select jd.Account, isnull(jd.Debit, 0) - isnull(jd.Credit, 0) curAmt, 0 prevAmt from JDT1 jd where jd.RefDate <= '2013-01-31'
		
		union 
		select jd.Account, 0 curAmt, isnull(jd.Debit, 0) - isnull(jd.Credit, 0) prevAmt from JDT1 jd where jd.RefDate <= @dateLM
	)T   join @result re on re.LocalAcctCode = T.Account collate SQL_Latin1_General_CP850_CI_AS

	 
	 select SUM(ls.curAmt) from @las ls --where ls.Levels <= @level 
	 
	 
end	

-- rpt_AI_AccountBalance '01-31-2013', 5

